﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web;

namespace Demo
{
    /// <summary>
    /// 数据库的访问层
    /// </summary>
    public class DbHelper
    {
        /// <summary>
        /// 定义数据库访问字符串
        /// </summary>
        public readonly string strConn = "server=192.168.1.222;uid=sa;pwd=123456;database=yncs";


        /*
        表结构
        
        CREATE TABLE [dbo].[Customer](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[fingerprint] [nvarchar](4000) NULL,
	[name] [nvarchar](50) NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

         
         */

        #region 执行查询，返回DataTable对象 -----------------------------------------
        public DataTable GetTable(string sqlString)
        {
            return GetTable(sqlString, null);
        }
        public DataTable GetTable(string sqlString, SqlParameter[] SqlPam)
        {
            return GetTable(sqlString, SqlPam, CommandType.Text);
        }
        /// <summary>
        /// 执行查询、返回DataTable对象
        /// </summary>
        /// <param name="SqlString"></param>
        /// <param name="SqlPam"></param>
        /// <param name="cmdtype"></param>
        /// <returns></returns>
        public DataTable GetTable(string sqlString, SqlParameter[] SqlPam, CommandType cmdtype)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                SqlDataAdapter da = new SqlDataAdapter(sqlString, conn);
                da.SelectCommand.CommandType = cmdtype;
                if (SqlPam != null)
                {
                    da.SelectCommand.Parameters.AddRange(SqlPam);
                }
                da.Fill(dt);
            }
            return dt;
        }
        public DataTable GetTable(String sqlString, int pageIndex, int pageSize, string order)
        {
            StringBuilder str = new StringBuilder();
            str.Append("select * from(select ROW_NUMBER() OVER(ORDER BY " + order + ") as rowNumber,* from (");
            str.Append("select *,0 as page_Index from (" + sqlString + ") as alawliet");
            str.Append(" ) as t ) as t2");
            str.Append(" where rowNumber between " + (((pageIndex - 1) * pageSize) + 1) + " and " + pageIndex * pageSize + " ");
            var dt = GetTable(str.ToString());
            dt.Columns.Remove("rowNumber");
            return dt;
        }
        #endregion

        #region 执行查询,返回DataSet对象
        public DataSet GetDataSet(string sqlString)
        {
            return GetDataSet(sqlString, null);
        }
        public DataSet GetDataSet(string sqlString, SqlParameter[] sqlPam)
        {
            return GetDataSet(sqlString, sqlPam, CommandType.Text);
        }
        public DataSet GetDataSet(string sqlString, SqlParameter[] sqlPam, CommandType cmd)
        {
            DataSet ds = new DataSet();
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                SqlDataAdapter da = new SqlDataAdapter(sqlString, conn);
                da.SelectCommand.CommandType = cmd;
                if (sqlPam != null)
                {
                    da.SelectCommand.Parameters.AddRange(sqlPam);
                }
                da.Fill(ds);
            }
            return ds;
        }
        #endregion

        #region 执行非查询存储过程和SQL语句 -----------------------------------------
        public int ExcuteProc(string procName)
        {
            return ExcuteSQL(procName, null, CommandType.StoredProcedure);
        }
        public int ExcuteProc(string procName, SqlParameter[] sqlPam)
        {
            return ExcuteSQL(procName, sqlPam, CommandType.StoredProcedure);
        }
        public int ExcuteSQL(string sqlString)
        {
            int count = ExcuteSQL(sqlString, null);
            return count;
        }
        public int ExcuteSQL(string sqlString, SqlParameter[] sqlPam)
        {
            int count = ExcuteSQL(sqlString, sqlPam, CommandType.Text);
            return count;
        }

        /// <summary>
        /// 执行非查询存储过程和SQL语句,并返回影响行数
        /// 增、删、改
        /// </summary>
        /// <param name="sqlString">要执行的SQL语句</param>
        /// <param name="cmdType">Command类型</param>
        /// <param name="sqlPam">参数列表，没有参数录入Null</param>
        /// <returns>返回影响行数</returns>
        public int ExcuteSQL(string sqlString, SqlParameter[] sqlPam, CommandType cmdType)
        {
            int i = 0;
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                SqlCommand cmd = new SqlCommand(sqlString, conn);
                cmd.CommandType = cmdType;
                if (sqlPam != null)
                {
                    cmd.Parameters.AddRange(sqlPam);
                }
                conn.Open();
                i = cmd.ExecuteNonQuery();
                conn.Close();
            }
            return i;
        }
        #endregion

        #region 执行查询，返回第一行第一列 -----------------------------------------
        /// <summary>
        /// 调用不带参数的存储过程，返回单个值
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <returns></returns>
        public object GetObjectByProc(string procName)
        {
            return GetObjectByProc(procName, null);
        }
        /// <summary>
        /// 调用带参数的存储过程，返回单个值
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="sqlPam"></param>
        /// <returns></returns>
        public object GetObjectByProc(string procName, SqlParameter[] sqlPam)
        {
            return GetObject(procName, sqlPam, CommandType.StoredProcedure);
        }
        /// <summary>
        /// 根据SQL语句获取单个值
        /// </summary>
        /// <param name="sqlString"></param>
        /// <returns></returns>
        public object GetObjectBySQL(string sqlString)
        {
            return GetObjectBySQL(sqlString, null);
        }
        /// <summary>
        /// 根据SQL语句和参数值获取单个值
        /// </summary>
        /// <param name="sqlString"></param>
        /// <param name="sqlPam"></param>
        /// <returns></returns>
        public object GetObjectBySQL(string sqlString, SqlParameter[] sqlPam)
        {
            return GetObject(sqlString, sqlPam, CommandType.Text);
        }
        /// <summary>
        /// 执行SQL语句，返回首列首行
        /// </summary>
        /// <param name="sqlString"></param>
        /// <param name="sqlPam"></param>
        /// <param name="cmdType"></param>
        /// <returns></returns>
        public object GetObject(string sqlString, SqlParameter[] sqlPam, CommandType cmdType)
        {
            object o = null;
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                SqlCommand cmd = new SqlCommand(sqlString, conn);
                cmd.CommandType = cmdType;
                if (sqlPam != null)
                {
                    cmd.Parameters.AddRange(sqlPam);
                }
                conn.Open();
                o = cmd.ExecuteScalar();
                conn.Close();
            }
            return o;
        }


        /// <summary>
        /// 执行SQL语句，返回首列首行
        /// </summary>
        /// <param name="sqlString"></param>
        /// <param name="sqlPam"></param> 
        /// <returns></returns>
        public object GetObject(string sqlString, SqlParameter[] sqlPam)
        {
            object o = null;
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                SqlCommand cmd = new SqlCommand(sqlString, conn);
                cmd.CommandType = CommandType.Text;
                if (sqlPam != null)
                {
                    cmd.Parameters.AddRange(sqlPam);
                }
                conn.Open();
                o = cmd.ExecuteScalar();
                conn.Close();
            }
            return o;
        }

        #endregion

        #region 查询获取DataReader-----------------------------------------
        /// <summary>
        /// 调用不带参数的存储过程，返回DataReader对象
        /// </summary>
        /// <param name="procName"></param>
        /// <returns></returns>
        public SqlDataReader GetReaderByPro(string procName)
        {
            return GetReaderByPro(procName, null);
        }
        /// <summary>
        /// 调用带参数的存储过程，返回DataReader对象
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="sqlPam"></param>
        /// <returns></returns>
        public SqlDataReader GetReaderByPro(string procName, SqlParameter[] sqlPam)
        {
            return GetReader(procName, sqlPam, CommandType.StoredProcedure);
        }
        /// <summary>
        /// 执行一条SQL语句，返回DataReader对象
        /// </summary>
        /// <param name="sqlString"></param>
        /// <returns></returns>
        public SqlDataReader GetReaderBySQL(string sqlString)
        {
            return GetReaderBySQL(sqlString, null);
        }
        /// <summary>
        /// 执行一条SQL语句和参数，返回DataReader对象
        /// </summary>
        /// <param name="sqlString"></param>
        /// <param name="sqlPam"></param>
        /// <returns></returns>
        public SqlDataReader GetReaderBySQL(string sqlString, SqlParameter[] sqlPam)
        {
            return GetReader(sqlString, sqlPam, CommandType.Text);
        }
        /// <summary>
        /// 执行SQL语句或存储过程，获取DataReader对象
        /// </summary>
        /// <param name="sqlString">查询语句</param>
        /// <param name="sqlPam">参数</param>
        /// <param name="cmdType">Command类型</param>
        /// <returns>DataReader对象</returns>
        public SqlDataReader GetReader(string sqlString, SqlParameter[] sqlPam, CommandType cmdType)
        {
            SqlDataReader sqldr = null;
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                SqlCommand cmd = new SqlCommand(sqlString, conn);
                cmd.CommandType = cmdType;
                if (sqlPam != null)
                {
                    cmd.Parameters.AddRange(sqlPam);
                }
                conn.Open();
                cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            return sqldr;
        }
        #endregion

        #region 批量插入数据
        /// <summary>
        /// 往数据库中批量插入数据
        /// </summary>
        /// <param name="sourceDt">数据源</param>
        /// <param name="targetTable">服务器上的目标表</param>
        public void BulkToDB(DataTable sourceDt, string targetTable)
        {
            SqlConnection conn = new SqlConnection(strConn);
            SqlBulkCopy bulkcopy = new SqlBulkCopy(conn);
            bulkcopy.DestinationTableName = targetTable;
            bulkcopy.BatchSize = sourceDt.Rows.Count;
            try
            {
                conn.Open();
                if (sourceDt != null && sourceDt.Rows.Count > 0)
                {
                    bulkcopy.WriteToServer(sourceDt);
                }
            }
            catch (Exception e)
            {
                conn.Close();
                if (bulkcopy != null)
                {
                    bulkcopy.Close();
                }
            }
        }
        #endregion
    }
}